{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 准备工作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "阿里研究院\n",
    "阿里健康\n",
    "阿里巴巴商学院\n",
    "阿里数据\n",
    "\n",
    "腾讯金融科技\n",
    "腾讯研究院\n",
    "腾讯媒体研究院\n",
    "腾讯云启研究院\n",
    "酷鹅用户研究院\n",
    "'''\n",
    "公众号 = \"蚂蚁金服科技\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "fn = { \"output\" : { \"公众号_htm_snippets\": \"data_raw_src/公众号_htm_snippets_{公众号}.tsv\",\n",
    "                    \"公众号_df\": \"data_raw_src/公众号_df_{公众号}.tsv\",\n",
    "                    \"公众号_xlsx\": \"data_sets/公众号_url_{公众号}.xlsx\" } \\\n",
    "      }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 采集公众号（selenium）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from lxml.html import fromstring\n",
    "import time\n",
    "from random import random\n",
    "\n",
    "# when selenium main_content is used\n",
    "# Parses an HTML document from a string constant.  Returns the root nood\n",
    "# root = fromstring(df.loc[1,\"html_snippets\"]) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 使用Selenium\n",
    "* 要更改 opts.binary_location 至自己本地的Chrome浏览器，建议portable\n",
    "* Chrome浏览器 和 chromedriver.exe要同版本号到小数后一位\n",
    "* 要确保可以 开启浏览器机器人\n",
    "* 要确保浏览器机器人 可以打开网页 driver.get(\"https://mp.weixin.qq.com\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:18: DeprecationWarning: use options instead of chrome_options\n"
     ]
    }
   ],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.desired_capabilities import DesiredCapabilities\n",
    "\n",
    "#caps=dict()\n",
    "#caps[\"pageLoadStrategy\"] = \"none\"   # Do not wait for full page load\n",
    "\n",
    "opts = webdriver.ChromeOptions()\n",
    "opts.add_argument('--no-sandbox')#解决DevToolsActivePort文件不存在的报错\n",
    "opts.add_argument('window-size=1920x3000') #指定浏览器分辨率\n",
    "opts.add_argument('--disable-gpu') #谷歌文档提到需要加上一这个属性来规避bug\n",
    "opts.add_argument('--hide-scrollbars') #隐藏滚动条, 应对些特殊页面\n",
    "#opts.add_argument('blink-settings=imagesEnabled=false') #不加载图片, 提升速度\n",
    "#opts.add_argument('--headless') #浏览器不提供可视化页面. linux下如果系统不支持可视化不加这条会启动失败\n",
    "\n",
    "opts.binary_location = r\"C:\\Users\\Administrator\\AppData\\Local\\Google\\Chrome\\Application\\chrome.exe\" #\"H:\\_coding_\\Gitee\\InternetNewMedia\\CapstonePrj2016\\chromedriver.exe\"  \n",
    "\n",
    "# \"H:\\_coding_\\Gitee\\InternetNewMedia\\CapstonePrj2016\\chromedriver.exe\"  \n",
    "driver = webdriver.Chrome( chrome_options = opts) #desired_capabilities=caps,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.get(\"https://mp.weixin.qq.com\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 填表登入"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "selenium 的定位方法\n",
    "* find_element_by_id &ensp;&ensp;&ensp;  根据标签id定位\n",
    "* find_element_by_name   &ensp;&ensp;&ensp; 根据标签的name定位\n",
    "* find_element_by_xpath  &ensp;&ensp;&ensp; 根据xpath定位\n",
    "* find_element_by_link_text  &ensp;&ensp;&ensp; 通过文字链接来定位元素\n",
    "* find_element_by_partial_link_text  &ensp;&ensp;&ensp;  通过文字链接来定位元素\n",
    "* find_element_by_tag_name  &ensp;&ensp;&ensp;  根据标签的名字定位\n",
    "* find_element_by_class_name  &ensp;&ensp;&ensp; 通过class name 定位\n",
    "* find_element_by_css_selector  &ensp;&ensp;&ensp;  根据元素属性来定位"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "payload =  {\"account\": \"1210680471@qq.com\", \"password\": \"1210680471ly\"}\n",
    "# payload =  {\"account\": \"NFUHacks@163.com\", \"password\": \"NFU706947580\"}\n",
    "driver.find_element_by_xpath('//div[@class=\"login__type__container login__type__container__scan\"]/a').click()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "WebDriver 常用方法：\n",
    "* clear()清楚文本\n",
    "* send_keys(values)模拟按键输入\n",
    "* click()模拟点击\n",
    "* submit模拟提交"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"account\"]').clear()\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"account\"]').send_keys(payload['account'])\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"password\"]').clear()\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"password\"]').send_keys(payload['password'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//div[@class=\"login_btn_panel\"]/a').click()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 点选单"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "其他常用方法\n",
    "* size：返回元素的尺寸\n",
    "* text：获取元素的文本\n",
    "* get_attribute：获取属性值  &ensp;&ensp;&ensp; get_attribute('innerHTML')获取元素内的全部HTML\n",
    "* is_displayed()：设置该元素用户是否可见"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'展开'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//a[@id=\"m_open\"]')\n",
    "element.click()\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.execute_script(\"window.scrollTo(0,document.body.scrollHeight)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://mp.weixin.qq.com/cgi-bin/appmsg?begin=0&count=10&t=media/appmsg_list&type=10&action=list&token=1873905475&lang=zh_CN'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//li[@title[contains(.,\"素材管理\")]]/a') \n",
    "# main_content = element.get_attribute('innerHTML')\n",
    "# main_content\n",
    "url2= element.get_attribute(\"href\")\n",
    "url2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.get(url2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 新建图文消息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"新建图文消息\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "main_content\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['CDwindow-7FEEA27DF509E348501E2695FA0E2902', 'CDwindow-2AAC4F9B5E6A398A4CF55E3A9F9DE7F2']\n"
     ]
    }
   ],
   "source": [
    "print (driver.window_handles)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 新建图文消息开了另一分视窗，所以要切换 switch_to \n",
    "driver.switch_to.window(driver.window_handles[-1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 超链接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                超链接              \n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"超链接\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "选择其他公众号\n"
     ]
    }
   ],
   "source": [
    "# 点 选择其他公众号\n",
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"选择其他公众号\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//form//div[@class=\"inner_link_account_area\"]//input[@class=\"weui-desktop-form__input\"]').clear()\n",
    "driver.find_element_by_xpath('//form//div[@class=\"inner_link_account_area\"]//input[@class=\"weui-desktop-form__input\"]').send_keys(公众号)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<div class=\"weui-desktop-icon weui-desktop-icon__inputSearch weui-desktop-icon__small\"><!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <svg width=\"16\" height=\"16\" viewBox=\"0 0 16 16\" xmlns=\"http://www.w3.org/2000/svg\"><path d=\"M11.33 10.007l4.273 4.273a.502.502 0 0 1 .005.709l-.585.584a.499.499 0 0 1-.709-.004L10.046 11.3a6.278 6.278 0 1 1 1.284-1.294zm.012-3.729a5.063 5.063 0 1 0-10.127 0 5.063 5.063 0 0 0 10.127 0z\"></path></svg> <!----> <!----> <!----> <!----></div>\n"
     ]
    }
   ],
   "source": [
    "# 点放大镜搜\n",
    "element = driver.find_element_by_xpath('//button[@class=\"weui-desktop-icon-btn weui-desktop-search__btn\"]')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ibic8Ff0hib2zM1PNggypmobkam18vP1Nh7kaBU77zrJ8WD9vd8nj4uicvXIFXu1Ek0ScZGia91Clw/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服科技</strong> <i class=\"inner_link_account_wechat\">微信号：Ant-Techfin</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/rS76otFH2pNlicVQBHK655aKVDMOs2fbSdQb0Zxic4ibIGJUNAayAjy3yCeWqSFe4GVNhExtpNt4opiavkicHGHI1hQ/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服</strong> <i class=\"inner_link_account_wechat\">微信号：mayi-jinfu</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/ibouibdiaMA9KyndRqTmA3nJJpuL3eCmHqF9L5FzIQ3aoG8fH3gZT8dT3uZyD9YYvA9dlwTsHDfEtgW9hVt5jKHkg/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">山东蚂蚁金服网络科技有限公司</strong> <i class=\"inner_link_account_wechat\">微信号：SDMYJF_FWH</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">服务号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/cia3tMb8xpGJSuySfict4E8oUlkK2307gQnOiaa93h83mib5Wcp3oKp570jgcWdSBiatwvU9nT2Hgs9Qzg0tvJUU8sw/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服花呗</strong> <i class=\"inner_link_account_wechat\">微信号：mayi_huabei</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">服务号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/njNOiayhTg0vmmugt6hiaJVic45dibuWblwcsPibP9D2E8iaHbDWSW86DnhXS2faddpqicd9dKL3t0AZF6TXlzCdEys2A/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服安全响应中心</strong> <i class=\"inner_link_account_wechat\">微信号：未设置</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li>\n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//ul[@class=\"inner_link_account_list\"]')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "公众号SERP = main_content\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 解析\n",
    "root = fromstring(公众号SERP) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "主 = root.xpath('//li[@class=\"inner_link_account_item\"]')\n",
    "\n",
    "account_list = []\n",
    "for e in 主:\n",
    "    account_nickname = e.xpath('./div/strong[@class=\"inner_link_account_nickname\"]')[0].text\n",
    "    account_wechat = e.xpath('./div/i[@class=\"inner_link_account_wechat\"]')[0].text\n",
    "    account_img = e.xpath('./div/img/@src')[0]\n",
    "    account = {\"nickname\": account_nickname, \"wechat\": account_wechat, \"img\": account_img,}\n",
    "    account_list.append(account)\n",
    "\n",
    "df_account = pd.DataFrame(account_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nickname</th>\n",
       "      <th>wechat</th>\n",
       "      <th>img</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>蚂蚁金服科技</td>\n",
       "      <td>微信号：Ant-Techfin</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>蚂蚁金服</td>\n",
       "      <td>微信号：mayi-jinfu</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/rS76otFH2pNlicV...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>山东蚂蚁金服网络科技有限公司</td>\n",
       "      <td>微信号：SDMYJF_FWH</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/ibouibdiaMA9Kyn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>蚂蚁金服花呗</td>\n",
       "      <td>微信号：mayi_huabei</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/cia3tMb8xpGJSuy...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>蚂蚁金服安全响应中心</td>\n",
       "      <td>微信号：未设置</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/njNOiayhTg0vmmu...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         nickname           wechat  \\\n",
       "0          蚂蚁金服科技  微信号：Ant-Techfin   \n",
       "1            蚂蚁金服   微信号：mayi-jinfu   \n",
       "2  山东蚂蚁金服网络科技有限公司   微信号：SDMYJF_FWH   \n",
       "3          蚂蚁金服花呗  微信号：mayi_huabei   \n",
       "4      蚂蚁金服安全响应中心          微信号：未设置   \n",
       "\n",
       "                                                 img  \n",
       "0  http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ib...  \n",
       "1  http://mmbiz.qpic.cn/mmbiz_png/rS76otFH2pNlicV...  \n",
       "2  http://mmbiz.qpic.cn/mmbiz_png/ibouibdiaMA9Kyn...  \n",
       "3  http://mmbiz.qpic.cn/mmbiz_png/cia3tMb8xpGJSuy...  \n",
       "4  http://mmbiz.qpic.cn/mmbiz_png/njNOiayhTg0vmmu...  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ibic8Ff0hib2zM1PNggypmobkam18vP1Nh7kaBU77zrJ8WD9vd8nj4uicvXIFXu1Ek0ScZGia91Clw/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服科技</strong> <i class=\"inner_link_account_wechat\">微信号：Ant-Techfin</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div>\n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//ul[@class=\"inner_link_account_list\"]/li')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n跳转_input = driver.find_element_by_xpath(\\'//span[@class=\"weui-desktop-pagination__form\"]/input\\')\\n跳转_a = driver.find_element_by_xpath(\\'//span[@class=\"weui-desktop-pagination__form\"]/a\\')\\n跳转_input.clear()\\n跳转_input.send_keys(2)\\n跳转_a.click()\\n'"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 跳转testing\n",
    "'''\n",
    "跳转_input = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/input')\n",
    "跳转_a = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/a')\n",
    "跳转_input.clear()\n",
    "跳转_input.send_keys(2)\n",
    "跳转_a.click()\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 99]\n",
      "False\n"
     ]
    }
   ],
   "source": [
    "# 跳转上限\n",
    "l_e = driver.find_elements_by_xpath('//label[@class=\"weui-desktop-pagination__num\"]')\n",
    "l_e_int  = [int(x.text) for x in l_e] \n",
    "print (l_e_int)\n",
    "print (l_e_int[0]==l_e_int[-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50]\n"
     ]
    }
   ],
   "source": [
    "#pages = list(range(l_e_int[0],l_e_int[-1]+1 ))\n",
    "#print(pages[0:2])\n",
    "pages = list(range(1,l_e_int[-1]+1 ))\n",
    "print(pages[0:50])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 循环/遍历"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# global varialbes \n",
    "html_raw = dict()\n",
    "main_content =\"\"\n",
    "element = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_pages (pages):\n",
    "    for p in pages:\n",
    "        print (p,end='\\t')\n",
    "\n",
    "        跳转_input = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/input')\n",
    "        跳转_a = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/a')\n",
    "        跳转_input.clear()\n",
    "        跳转_input.send_keys(p)\n",
    "        跳转_a.click()\n",
    "\n",
    "        time.sleep(2+5*random())\n",
    "\n",
    "        element = driver.find_element_by_xpath('//div[@class=\"inner_link_article_list\"]')\n",
    "        main_content = element.get_attribute('innerHTML')\n",
    "        #print(main_content)\n",
    "        html_raw[p] = main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1/t2/t3/t4/t5/t6/t7/t8/t9/t10/t11/t12/t13/t14/t15/t16/t17/t18/t19/t20/t21/t22/t23/t24/t25/t26/t27/t28/t29/t30/t31/t32/t33/t34/t35/t36/t37/t38/t39/t40/t41/t42/t43/t44/t45/t46/t47/t48/t49/t50/t51/t52/t53/t54/t55/t56/t57/t58/t59/t60/t61/t62/t63/t64/t65/t66/t67/t68/t69/t70/t71/t72/t73/t74/t75/t76/t77/t78/t79/t80/t81/t82/t83/t84/t85/t86/t87/t88/t89/t90/t91/t92/t93/t94/t95/t96/t97/t98/t99/t"
     ]
    }
   ],
   "source": [
    "process_pages(pages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>html_snippets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        html_snippets\n",
       "1   <div><label class=\"inner_link_article_item\"><s...\n",
       "2   <div><label class=\"inner_link_article_item\"><s...\n",
       "3   <div><label class=\"inner_link_article_item\"><s...\n",
       "4   <div><label class=\"inner_link_article_item\"><s...\n",
       "5   <div><label class=\"inner_link_article_item\"><s...\n",
       "..                                                ...\n",
       "95  <div><label class=\"inner_link_article_item\"><s...\n",
       "96  <div><label class=\"inner_link_article_item\"><s...\n",
       "97  <div><label class=\"inner_link_article_item\"><s...\n",
       "98  <div><label class=\"inner_link_article_item\"><s...\n",
       "99  <div><label class=\"inner_link_article_item\"><s...\n",
       "\n",
       "[99 rows x 1 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([html_raw]).T\n",
    "df.columns = [\"html_snippets\"]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stored 'html_raw' (dict)\n"
     ]
    }
   ],
   "source": [
    "%store html_raw\n",
    "import pickle \n",
    "filehandler = open(\"html_raw\", 'wb') \n",
    "pickle.dump(html_raw, filehandler)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "65\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>html_snippets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        html_snippets\n",
       "12  <div><label class=\"inner_link_article_item\"><s...\n",
       "67  <div><label class=\"inner_link_article_item\"><s...\n",
       "68  <div><label class=\"inner_link_article_item\"><s...\n",
       "69  <div><label class=\"inner_link_article_item\"><s...\n",
       "70  <div><label class=\"inner_link_article_item\"><s...\n",
       "71  <div><label class=\"inner_link_article_item\"><s...\n",
       "72  <div><label class=\"inner_link_article_item\"><s...\n",
       "73  <div><label class=\"inner_link_article_item\"><s...\n",
       "74  <div><label class=\"inner_link_article_item\"><s...\n",
       "75  <div><label class=\"inner_link_article_item\"><s...\n",
       "76  <div><label class=\"inner_link_article_item\"><s...\n",
       "77  <div><label class=\"inner_link_article_item\"><s...\n",
       "78  <div><label class=\"inner_link_article_item\"><s...\n",
       "79  <div><label class=\"inner_link_article_item\"><s...\n",
       "80  <div><label class=\"inner_link_article_item\"><s...\n",
       "81  <div><label class=\"inner_link_article_item\"><s...\n",
       "82  <div><label class=\"inner_link_article_item\"><s...\n",
       "83  <div><label class=\"inner_link_article_item\"><s...\n",
       "84  <div><label class=\"inner_link_article_item\"><s...\n",
       "85  <div><label class=\"inner_link_article_item\"><s...\n",
       "86  <div><label class=\"inner_link_article_item\"><s...\n",
       "87  <div><label class=\"inner_link_article_item\"><s...\n",
       "88  <div><label class=\"inner_link_article_item\"><s...\n",
       "89  <div><label class=\"inner_link_article_item\"><s...\n",
       "90  <div><label class=\"inner_link_article_item\"><s...\n",
       "91  <div><label class=\"inner_link_article_item\"><s...\n",
       "92  <div><label class=\"inner_link_article_item\"><s...\n",
       "93  <div><label class=\"inner_link_article_item\"><s...\n",
       "94  <div><label class=\"inner_link_article_item\"><s...\n",
       "95  <div><label class=\"inner_link_article_item\"><s...\n",
       "96  <div><label class=\"inner_link_article_item\"><s...\n",
       "97  <div><label class=\"inner_link_article_item\"><s...\n",
       "98  <div><label class=\"inner_link_article_item\"><s...\n",
       "99  <div><label class=\"inner_link_article_item\"><s..."
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out = df[~df.duplicated()]\n",
    "print (len(df_out))\n",
    "df[df.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[12, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[12,\n",
       " 67,\n",
       " 68,\n",
       " 69,\n",
       " 70,\n",
       " 71,\n",
       " 72,\n",
       " 73,\n",
       " 74,\n",
       " 75,\n",
       " 76,\n",
       " 77,\n",
       " 78,\n",
       " 79,\n",
       " 80,\n",
       " 81,\n",
       " 82,\n",
       " 83,\n",
       " 84,\n",
       " 85,\n",
       " 86,\n",
       " 87,\n",
       " 88,\n",
       " 89,\n",
       " 90,\n",
       " 91,\n",
       " 92,\n",
       " 93,\n",
       " 94,\n",
       " 95,\n",
       " 96,\n",
       " 97,\n",
       " 98,\n",
       " 99]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try_again = list(df[df.duplicated()].index)\n",
    "print(try_again)\n",
    "try_again = try_again + list (set(pages).difference(set(df.index.values)))\n",
    "try_again"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 暂存档"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = fn [\"output\"] [\"公众号_htm_snippets\"] \n",
    "df_out.to_csv(filename.format(公众号=公众号), sep=\"\\t\", encoding=\"utf8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,7,5,5,5,5,5,5,8,6,5,5,5,5,5,5,5,5,5,5,5,5,5,5,6,5,5,5,5,5,5,5,5,5,5,6,9,6,6,5,5,6,7,6,5,5,6,7,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,16,"
     ]
    }
   ],
   "source": [
    "def parse_html_snippets(_snippet_):\n",
    "    root = fromstring(_snippet_) \n",
    "    title = [x.text for x in root.xpath('//div[@class=\"inner_link_article_title\"]')]\n",
    "    create_time = [x.text for x in root.xpath('//div[@class=\"inner_link_article_date\"]')]\n",
    "    link = [x for x in root.xpath('//a/@href')]\n",
    "    _df_ = pd.DataFrame({\"title\":title, \"create_time\": create_time, \"link\":link})\n",
    "    return(_df_)\n",
    "    \n",
    "l_df = []\n",
    "for p in pages:\n",
    "    _df_ = parse_html_snippets(df.loc[p,\"html_snippets\"])\n",
    "    print (len(_df_), end=\",\")\n",
    "    l_df.append(_df_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>彭博社：金融科技成银行抗疫法宝，蚂蚁科技咨询量猛增400%</td>\n",
       "      <td>2020-05-15</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>美女妈妈的十年阿里路，从一线研发到技术leader</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路</td>\n",
       "      <td>2020-04-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>学术与工程的结合 蚂蚁金服多篇论文入选人工智能顶会AAAI 2020</td>\n",
       "      <td>2020-02-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>阿里巴巴向疫情类APP免费开放所有能力，助力科技抗疫</td>\n",
       "      <td>2020-02-08</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>支付宝工程师如何在13个小时内上线“医护人员保障金”？</td>\n",
       "      <td>2020-02-07</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>在家办公3天后，我们有这些经验可以和你们分享</td>\n",
       "      <td>2020-02-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>支付宝旗下知识管理工具语雀免费开放，助力企业在家办公</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>从上千篇论文脱颖而出，支付宝论文凭什么获得WWW2020评委认可？</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     title create_time  \\\n",
       "0            彭博社：金融科技成银行抗疫法宝，蚂蚁科技咨询量猛增400%  2020-05-15   \n",
       "1        蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”  2020-05-13   \n",
       "2                美女妈妈的十年阿里路，从一线研发到技术leader  2020-05-09   \n",
       "3   支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路  2020-04-27   \n",
       "4      【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "5       学术与工程的结合 蚂蚁金服多篇论文入选人工智能顶会AAAI 2020  2020-02-13   \n",
       "6               阿里巴巴向疫情类APP免费开放所有能力，助力科技抗疫  2020-02-08   \n",
       "7              支付宝工程师如何在13个小时内上线“医护人员保障金”？  2020-02-07   \n",
       "8                   在家办公3天后，我们有这些经验可以和你们分享  2020-02-06   \n",
       "9               支付宝旗下知识管理工具语雀免费开放，助力企业在家办公  2020-02-04   \n",
       "10       从上千篇论文脱颖而出，支付宝论文凭什么获得WWW2020评委认可？  2020-04-15   \n",
       "\n",
       "                                                 link  \n",
       "0   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "1   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "2   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "3   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "4   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "5   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "6   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "7   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "8   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "9   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "10  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out = pd.concat(l_df).reset_index(drop=True)\n",
    "df_url_out.loc[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_url_out.to_excel(\"蚂蚁金服微信公众翻页数据.xlsx\",\\\n",
    "                sheet_name=\"翻页\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>885</th>\n",
       "      <td>30家客户案例带你看蚂蚁金服全面科技开放</td>\n",
       "      <td>2018-09-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>886</th>\n",
       "      <td>攻略 | 蚂蚁金服的年度盛宴ATEC来了，想好怎么玩了吗？</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>全方位剧透 | 2018 ATEC超强阵容金融科技开放峰会即将启幕</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888</th>\n",
       "      <td>ATEC倒计时01天 ！ 看重庆农商行如何打造全行级全智能平台</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>889</th>\n",
       "      <td>ATEC倒计时02天 | “小微金融专家”台州银行如何利用科技创新提升服务</td>\n",
       "      <td>2018-09-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     title create_time  \\\n",
       "885                   30家客户案例带你看蚂蚁金服全面科技开放  2018-09-19   \n",
       "886          攻略 | 蚂蚁金服的年度盛宴ATEC来了，想好怎么玩了吗？  2018-09-18   \n",
       "887      全方位剧透 | 2018 ATEC超强阵容金融科技开放峰会即将启幕  2018-09-18   \n",
       "888        ATEC倒计时01天 ！ 看重庆农商行如何打造全行级全智能平台  2018-09-18   \n",
       "889  ATEC倒计时02天 | “小微金融专家”台州银行如何利用科技创新提升服务  2018-09-17   \n",
       "\n",
       "                                                  link  \n",
       "885  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "886  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "887  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "888  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "889  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>value</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>美女妈妈的十年阿里路，从一线研发到技术leader</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>在家办公3天后，我们有这些经验可以和你们分享</td>\n",
       "      <td>2020-02-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>从上千篇论文脱颖而出，支付宝论文凭什么获得WWW2020评委认可？</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>校招季丨支付宝有一个疯起来连自己都要打的团队，你要加入吗</td>\n",
       "      <td>2020-04-14</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>882</th>\n",
       "      <td>Basement 后端云服务正式亮相云栖大会</td>\n",
       "      <td>2018-09-20</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>883</th>\n",
       "      <td>蚂蚁金服CTO程立云栖解析1+9个“支付宝”：“科技是暖的，世界是平的！”</td>\n",
       "      <td>2018-09-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>884</th>\n",
       "      <td>11岁姑娘挑战8分钟编程小程序！蚂蚁金服董事长井贤栋：欢迎加入支付宝！</td>\n",
       "      <td>2018-09-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>886</th>\n",
       "      <td>攻略 | 蚂蚁金服的年度盛宴ATEC来了，想好怎么玩了吗？</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>889</th>\n",
       "      <td>ATEC倒计时02天 | “小微金融专家”台州银行如何利用科技创新提升服务</td>\n",
       "      <td>2018-09-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>517 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       title create_time  \\\n",
       "value                                                      \n",
       "2                  美女妈妈的十年阿里路，从一线研发到技术leader  2020-05-09   \n",
       "4        【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "8                     在家办公3天后，我们有这些经验可以和你们分享  2020-02-06   \n",
       "10         从上千篇论文脱颖而出，支付宝论文凭什么获得WWW2020评委认可？  2020-04-15   \n",
       "11              校招季丨支付宝有一个疯起来连自己都要打的团队，你要加入吗  2020-04-14   \n",
       "...                                      ...         ...   \n",
       "882                   Basement 后端云服务正式亮相云栖大会  2018-09-20   \n",
       "883    蚂蚁金服CTO程立云栖解析1+9个“支付宝”：“科技是暖的，世界是平的！”  2018-09-19   \n",
       "884      11岁姑娘挑战8分钟编程小程序！蚂蚁金服董事长井贤栋：欢迎加入支付宝！  2018-09-19   \n",
       "886            攻略 | 蚂蚁金服的年度盛宴ATEC来了，想好怎么玩了吗？  2018-09-18   \n",
       "889    ATEC倒计时02天 | “小微金融专家”台州银行如何利用科技创新提升服务  2018-09-17   \n",
       "\n",
       "                                                    link  \n",
       "value                                                     \n",
       "2      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "4      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "8      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "10     http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "11     http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "...                                                  ...  \n",
       "882    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "883    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "884    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "886    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "889    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "\n",
       "[517 rows x 3 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# tagging 标记\n",
    "tagging_list = [\"\",\"马化腾\", \"腾讯\", \"微众银行\",\"腾讯复星\",\"腾讯风控\",\"腾讯支付\",\"WeChat\",\"We Remit\",\\\n",
    "                \"公益日\",\"红包\",\\\n",
    "                \"腾讯财付通\",\"鹅厂\",\"QQ钱包\",\"QQ红包\",\"QQ\",\\\n",
    "                \"只有一\",\"大咖\",\"听说\",\"图片\",\"照片\",\"小编\",\\\n",
    "                \"洗钱\", \"黑产\",\"被骗\",\"腾讯安全课\",\"诈骗\", \"炒股\",\"神秘兼职\",\"神秘组织\",\\\n",
    "                \"财付通\", \"品牌\",\\\n",
    "                \"收款\",\\\n",
    "                \"报告\",\\\n",
    "                \"银行卡\",\"理财\",\"选股\",\"发票\",\"基金\",\\\n",
    "                \"区块链\",\"金融云\",\"O2O\",\"农产品\",\"家乡\",\\\n",
    "                \"数据\", \"数据赋能\", \"智能\", \"数字孪生\", \"智慧大脑\",\\\n",
    "                \"出行\",\"乘车\",\"公交\",\"乘车码\", \"智慧地铁\",\\\n",
    "                \"高峰论坛\", \"智库\",\\\n",
    "                \"央行\",\"新规\", \\\n",
    "                \"微信\", \"微信支付\", \"跨境支付\", \"移动支付\",\"非银行支付\",\"电子支付\",\\\n",
    "                \"互联网金融\", \"金融科技\",\"互联网＋\",\"互联网+金融\",\"普惠金融\",\"虚拟银行\",\\\n",
    "                \"开放\",\"生态\",\"复杂\",\"互联网思维\",\"全球合作伙伴\",\\\n",
    "                \"联合国\", \"城市\", \"粤港澳大湾区\", \"平台\", \"可持续发展\", \"未来\", \"绿色\",\\\n",
    "                \"医护\",\"防护服\",\"小时\",\"武汉\",\"危机\",\"新冠肺炎\", \"疫\", \"疫情\", \"复工\",\"停课\",\"宅经济\",\\\n",
    "                \"基建\",\"新基建\"] #overwritable\n",
    "\n",
    "v_v_list = []\n",
    "\n",
    "for tag in tagging_list:\n",
    "    index_list = df_url_out [ df_url_out.title.str.contains(tag) ].index.tolist()\n",
    "    v_v_pairs = pd.DataFrame({tag:index_list}).melt().set_index(\"value\")\n",
    "    v_v_list.append(v_v_pairs)\n",
    "\n",
    "df_cat = v_v_list[0]\n",
    "for d in v_v_list:\n",
    "    df_cat.update(d)\n",
    "    \n",
    "# 尚未标记内容\n",
    "df_url_out.loc [ df_cat.query('variable==\"\"').index ]"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "df_url_out.loc[53].link"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>学术与工程的结合 蚂蚁金服多篇论文入选人工智能顶会AAAI 2020</td>\n",
       "      <td>2020-02-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>阿里巴巴向疫情类APP免费开放所有能力，助力科技抗疫</td>\n",
       "      <td>2020-02-08</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>支付宝工程师如何在13个小时内上线“医护人员保障金”？</td>\n",
       "      <td>2020-02-07</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>在家办公3天后，我们有这些经验可以和你们分享</td>\n",
       "      <td>2020-02-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>支付宝旗下知识管理工具语雀免费开放，助力企业在家办公</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>885</th>\n",
       "      <td>30家客户案例带你看蚂蚁金服全面科技开放</td>\n",
       "      <td>2018-09-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>886</th>\n",
       "      <td>攻略 | 蚂蚁金服的年度盛宴ATEC来了，想好怎么玩了吗？</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>全方位剧透 | 2018 ATEC超强阵容金融科技开放峰会即将启幕</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888</th>\n",
       "      <td>ATEC倒计时01天 ！ 看重庆农商行如何打造全行级全智能平台</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>889</th>\n",
       "      <td>ATEC倒计时02天 | “小微金融专家”台州银行如何利用科技创新提升服务</td>\n",
       "      <td>2018-09-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>533 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     title create_time  \\\n",
       "55      学术与工程的结合 蚂蚁金服多篇论文入选人工智能顶会AAAI 2020  2020-02-13   \n",
       "56              阿里巴巴向疫情类APP免费开放所有能力，助力科技抗疫  2020-02-08   \n",
       "57             支付宝工程师如何在13个小时内上线“医护人员保障金”？  2020-02-07   \n",
       "58                  在家办公3天后，我们有这些经验可以和你们分享  2020-02-06   \n",
       "59              支付宝旗下知识管理工具语雀免费开放，助力企业在家办公  2020-02-04   \n",
       "..                                     ...         ...   \n",
       "885                   30家客户案例带你看蚂蚁金服全面科技开放  2018-09-19   \n",
       "886          攻略 | 蚂蚁金服的年度盛宴ATEC来了，想好怎么玩了吗？  2018-09-18   \n",
       "887      全方位剧透 | 2018 ATEC超强阵容金融科技开放峰会即将启幕  2018-09-18   \n",
       "888        ATEC倒计时01天 ！ 看重庆农商行如何打造全行级全智能平台  2018-09-18   \n",
       "889  ATEC倒计时02天 | “小微金融专家”台州银行如何利用科技创新提升服务  2018-09-17   \n",
       "\n",
       "                                                  link  \n",
       "55   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "56   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "57   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "58   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "59   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "..                                                 ...  \n",
       "885  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "886  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "887  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "888  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "889  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "\n",
       "[533 rows x 3 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out[df_url_out.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>彭博社：金融科技成银行抗疫法宝，蚂蚁科技咨询量猛增400%</td>\n",
       "      <td>2020-05-15</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>美女妈妈的十年阿里路，从一线研发到技术leader</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路</td>\n",
       "      <td>2020-04-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>30家客户案例带你看蚂蚁金服全面科技开放</td>\n",
       "      <td>2018-09-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>攻略 | 蚂蚁金服的年度盛宴ATEC来了，想好怎么玩了吗？</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>全方位剧透 | 2018 ATEC超强阵容金融科技开放峰会即将启幕</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>ATEC倒计时01天 ！ 看重庆农商行如何打造全行级全智能平台</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>ATEC倒计时02天 | “小微金融专家”台州银行如何利用科技创新提升服务</td>\n",
       "      <td>2018-09-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>357 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      title create_time  \\\n",
       "0             彭博社：金融科技成银行抗疫法宝，蚂蚁科技咨询量猛增400%  2020-05-15   \n",
       "1         蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”  2020-05-13   \n",
       "2                 美女妈妈的十年阿里路，从一线研发到技术leader  2020-05-09   \n",
       "3    支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路  2020-04-27   \n",
       "4       【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "..                                      ...         ...   \n",
       "357                    30家客户案例带你看蚂蚁金服全面科技开放  2018-09-19   \n",
       "358           攻略 | 蚂蚁金服的年度盛宴ATEC来了，想好怎么玩了吗？  2018-09-18   \n",
       "359       全方位剧透 | 2018 ATEC超强阵容金融科技开放峰会即将启幕  2018-09-18   \n",
       "360         ATEC倒计时01天 ！ 看重庆农商行如何打造全行级全智能平台  2018-09-18   \n",
       "361   ATEC倒计时02天 | “小微金融专家”台州银行如何利用科技创新提升服务  2018-09-17   \n",
       "\n",
       "                                                  link  \n",
       "0    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "1    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "2    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "3    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "4    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "..                                                 ...  \n",
       "357  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "358  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "359  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "360  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "361  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "\n",
       "[357 rows x 3 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out[~df_url_out.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>彭博社：金融科技成银行抗疫法宝，蚂蚁科技咨询量猛增400%</td>\n",
       "      <td>2020-05-15</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>疫</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>智能</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>美女妈妈的十年阿里路，从一线研发到技术leader</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路</td>\n",
       "      <td>2020-04-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>数据</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>885</th>\n",
       "      <td>30家客户案例带你看蚂蚁金服全面科技开放</td>\n",
       "      <td>2018-09-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>开放</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>886</th>\n",
       "      <td>攻略 | 蚂蚁金服的年度盛宴ATEC来了，想好怎么玩了吗？</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>全方位剧透 | 2018 ATEC超强阵容金融科技开放峰会即将启幕</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>开放</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888</th>\n",
       "      <td>ATEC倒计时01天 ！ 看重庆农商行如何打造全行级全智能平台</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>平台</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>889</th>\n",
       "      <td>ATEC倒计时02天 | “小微金融专家”台州银行如何利用科技创新提升服务</td>\n",
       "      <td>2018-09-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>890 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      title create_time  \\\n",
       "0             彭博社：金融科技成银行抗疫法宝，蚂蚁科技咨询量猛增400%  2020-05-15   \n",
       "1         蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”  2020-05-13   \n",
       "2                 美女妈妈的十年阿里路，从一线研发到技术leader  2020-05-09   \n",
       "3    支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路  2020-04-27   \n",
       "4       【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "..                                      ...         ...   \n",
       "885                    30家客户案例带你看蚂蚁金服全面科技开放  2018-09-19   \n",
       "886           攻略 | 蚂蚁金服的年度盛宴ATEC来了，想好怎么玩了吗？  2018-09-18   \n",
       "887       全方位剧透 | 2018 ATEC超强阵容金融科技开放峰会即将启幕  2018-09-18   \n",
       "888         ATEC倒计时01天 ！ 看重庆农商行如何打造全行级全智能平台  2018-09-18   \n",
       "889   ATEC倒计时02天 | “小微金融专家”台州银行如何利用科技创新提升服务  2018-09-17   \n",
       "\n",
       "                                                  link variable  \n",
       "0    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...        疫  \n",
       "1    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       智能  \n",
       "2    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "3    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       数据  \n",
       "4    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "..                                                 ...      ...  \n",
       "885  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       开放  \n",
       "886  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "887  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       开放  \n",
       "888  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       平台  \n",
       "889  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "\n",
       "[890 rows x 4 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o = df_url_out.join(df_cat).replace(\"\", np.nan).fillna(\"无法分类\")\n",
    "df_o"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [title, create_time, link, variable]\n",
       "Index: []"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o[df_o.title.str.contains(\"腾讯\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>variable</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>无法分类</th>\n",
       "      <td>517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>开放</th>\n",
       "      <td>155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>平台</th>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>区块链</th>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>未来</th>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>数据</th>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>智能</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融科技</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>生态</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>小时</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>疫情</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>城市</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>武汉</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>疫</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>红包</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>基金</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>报告</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>图片</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>普惠金融</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>智库</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>听说</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>医护</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>移动支付</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>全球合作伙伴</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          title\n",
       "variable       \n",
       "无法分类        517\n",
       "开放          155\n",
       "平台           46\n",
       "区块链          42\n",
       "未来           41\n",
       "数据           30\n",
       "智能           13\n",
       "金融科技          9\n",
       "生态            6\n",
       "小时            5\n",
       "疫情            5\n",
       "城市            4\n",
       "武汉            3\n",
       "疫             3\n",
       "红包            2\n",
       "基金            1\n",
       "报告            1\n",
       "图片            1\n",
       "普惠金融          1\n",
       "智库            1\n",
       "听说            1\n",
       "医护            1\n",
       "移动支付          1\n",
       "全球合作伙伴        1"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stats = df_o.groupby(by=\"variable\").agg({\"title\":\"count\"}).sort_values(by=\"title\", ascending=False)\n",
    "df_stats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 输出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_account.columns.name = \"rel_accounts\"\n",
    "df_o.columns.name = \"url_cat\"\n",
    "df_stats.columns.name = \"stats\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "_df_.columns.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the xlsxwriter workbook and worksheet objects.  \n",
    "with pd.ExcelWriter(fn[\"output\"][\"公众号_xlsx\"].format(公众号=公众号)) as writer:\n",
    "    workbook  = writer.book\n",
    "\n",
    "    for _df_ in [df_account, df_o, df_stats]:\n",
    "        _df_.to_excel(writer, sheet_name = _df_.columns.name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
